﻿using ConfExpert.Utility;
using DevTeam;
using SpreadsheetGear;
using System;
using System.Collections.Generic;
using System.Data;

namespace ConfExpert.Forms.Siparisler
{
    class PackingListNew
    {
        IWorkbook wb;
        int YuklemePartisiId;

        public PackingListNew(int YuklemePartisiId)
        {
            this.YuklemePartisiId = YuklemePartisiId;
            DataSet ds = Session.DataManager.ExecuteDataSet("GetPackingList", YuklemePartisiId, string.Empty, Session.CurrentUser.UserId);

            DataTable dtHeader = ds.Tables[0];
            DataTable dtList0 = ds.Tables[1];
            DataTable dtColumns = ds.Tables[2];
            int PLTemplateId = dtHeader.Rows[0]["PLTemplateId"].ToInt();

            wb = Factory.GetWorkbook();

            for (int sheetIndex = 0; sheetIndex < dtHeader.Rows.Count; sheetIndex++)
            {
                DataRow drHeader = dtHeader.Rows[sheetIndex];
                IWorksheet ws = wb.Worksheets.Add();

                ws.Name = "PL "
                    + drHeader["SayfaAdi"].ToStr()

                    + (dtHeader.Rows[0]["PLTemplateId"].ToInt() == 2 ? (drHeader["Termin"] != DBNull.Value ? string.Format(" {0:00}.{1:00}", drHeader["Termin"].ToDate().Day, drHeader["Termin"].ToDate().Month) : string.Empty).Replace("  ", " ") : string.Empty);

                ws.Tab.ColorIndex = 42 + sheetIndex;

                int bdStart = drHeader["Basla"].ToInt();
                int bdEnd = drHeader["Bitir"].ToInt();
                int dagilimId = drHeader["DagilimId"].ToInt();
                int packingListId = drHeader["PackingListId"].ToInt();
                DataTable dtList = dtList0.Select("PackingListId=" + packingListId).CopyToDataTable();
                bool BigBoxVar = dtList.Select("BigBoxNr>0").Length > 0;

                IRange r1;
                IRange r2;

                Dictionary<string, int> cols = new Dictionary<string, int>();
                int offset = 0;
                for (int colIndex = 0; colIndex < dtColumns.Rows.Count; colIndex++)
                {
                    string caption = dtColumns.Rows[colIndex]["Caption"].ToStr();
                    string columnName = dtColumns.Rows[colIndex]["COLUMN_NAME"].ToStr();

                    if (columnName == "*")
                    {
                        cols.Add("bd1", colIndex);
                        cols.Add("bd2", colIndex + bdEnd - bdStart);
                        cols.Add("total", cols["bd2"] + 1);
                        offset = bdEnd - bdStart + 1;

                        for (int bd = 0; bd < offset; bd++)
                        {
                            r2 = ws.Cells[0 + 1, colIndex + bd];
                            DataTable dagilim = RepositoryManager.dsLookups.Tables["Dagilim"];
                            string bdCaption = dagilim.Select("DagilimId=" + dagilimId)[0][string.Format("A{0:00}", bdStart + bd)].ToStr();
                            r2.Value = bdCaption;

                            Formatla1(r2, true);
                            r2.VerticalAlignment = VAlign.Top;
                        }

                        r1 = ws.Cells[0, cols["bd1"]];
                        r2 = ws.Cells[0, cols["bd2"]];
                        r1.Value = "SIZES";
                        r1.Font.Size = 15;
                        r1.HorizontalAlignment = HAlign.Center;
                        if (r1.Address != r2.Address)
                            ws.Cells[r1.Address + ":" + r2.Address].Merge();
                        Formatla1(r1, true);

                        r1 = ws.Cells[0, cols["total"]];
                        r2 = ws.Cells[0 + 1, cols["total"]];
                        r1.Value = "TOTAL";
                        ws.Cells[r1.Address + ":" + r2.Address].Merge();
                        Formatla1(r1, true);

                        if (BigBoxVar)
                        {
                            cols.Add("bigboxtotal", cols["total"] + 1);
                            r1 = ws.Cells[0, cols["bigboxtotal"]];
                            r2 = ws.Cells[0 + 1, cols["bigboxtotal"]];
                            r1.Value = "BIG BOX TOTAL";
                            ws.Cells[r1.Address + ":" + r2.Address].Merge();
                            Formatla1(r1, true);

                            offset++;
                        }
                    }
                    else
                    {
                        if (columnName == "BigBoxNr")
                            cols.Add("bigboxnr", colIndex);

                        r1 = ws.Cells[0, colIndex + offset];
                        r2 = ws.Cells[0 + 1, colIndex + offset];

                        r1.Value = caption;
                        ws.Cells[r1.Address + ":" + r2.Address].Merge();
                        Formatla1(r1, true);
                    }
                }
                cols.Add("son", ws.UsedRange.ColumnCount - 1);  //son sütun
                int backup = offset;

                offset = 0;
                for (int colIndex = 0; colIndex < dtColumns.Rows.Count; colIndex++)
                {
                    string columnName = dtColumns.Rows[colIndex]["COLUMN_NAME"].ToStr();

                    int ci = dtList.Columns.IndexOf(columnName);

                    if (columnName == "*")
                    {
                        offset = backup;
                        for (int rowIndex = 0; rowIndex < dtList.Rows.Count; rowIndex++)
                        {
                            for (int bd = 0; bd < offset; bd++)
                            {
                                r1 = ws.Cells[rowIndex + 2, colIndex + bd];
                                r1.Value = dtList.Rows[rowIndex][string.Format("A{0:00}", bdStart + bd)];
                                Formatla1(r1, true);
                            }

                            r1 = ws.Cells[rowIndex + 2, cols["total"]];
                            r1.HorizontalAlignment = HAlign.Center;
                            r1.Formula = "=SUM(" + ws.Cells[rowIndex + 2, cols["bd1"]].Address + ":" + ws.Cells[rowIndex + 2, cols["bd2"]].Address + ")";
                            r1.Font.Bold = false;
                        }
                    }
                    else
                    {
                        for (int rowIndex = 0; rowIndex < dtList.Rows.Count; rowIndex++)
                        {
                            r1 = ws.Cells[rowIndex + 2, colIndex + offset];
                            r1.Value = dtList.Rows[rowIndex][ci];
                            Formatla1(r1);

                            //r1.Interior.Color = Color.FromArgb(System.Drawing.Color.Blue.ToArgb());
                            //r2.Interior.Color = Color.FromArgb(System.Drawing.Color.Red.ToArgb());
                        }

                    }
                }

                ws.UsedRange.Borders.Weight = BorderWeight.Thin;
                ws.UsedRange.Borders[BordersIndex.InsideHorizontal].Weight =
                ws.UsedRange.Borders[BordersIndex.InsideVertical].Weight = BorderWeight.Hairline;


                if (BigBoxVar)
                {
                    int ri = 0;
                    int BBN = ws.Cells[0 + 2, cols["bigboxnr"]].Value.ToInt();
                    for (int rowIndex = 0; rowIndex <= dtList.Rows.Count; rowIndex++)
                    {
                        if (BBN != ws.Cells[rowIndex + 2, cols["bigboxnr"]].Value.ToInt())
                        {
                            IRange ir = ws.Cells[ws.Cells[ri + 2, cols["bigboxnr"]].Address + ":" + ws.Cells[rowIndex + 2 - 1, cols["bigboxnr"]].Address];
                            if (ir.RowCount > 1)
                                ir.Merge();
                            ir.VerticalAlignment = VAlign.Center;
                            ir.HorizontalAlignment = HAlign.Center;

                            r1 = ws.Cells[ri + 2, cols["bigboxtotal"]];
                            r1.Formula = "=SUM(" + ws.Cells[ri + 2, cols["total"]].Address + ":" + ws.Cells[rowIndex + 2 - 1, cols["total"]].Address + ")";

                            ir = ws.Cells[ws.Cells[ri + 2, cols["bigboxtotal"]].Address + ":" + ws.Cells[rowIndex + 2 - 1, cols["bigboxtotal"]].Address];
                            if (ir.RowCount > 1)
                                ir.Merge();
                            ir.VerticalAlignment = VAlign.Center;
                            ir.HorizontalAlignment = HAlign.Center;

                            ir = ws.Cells[ws.Cells[rowIndex + 2 - 1, 0].Address + ":" + ws.Cells[rowIndex + 2 - 1, cols["son"]].Address];
                            ir.Borders[BordersIndex.EdgeBottom].Weight = BorderWeight.Thin;

                            BBN = ws.Cells[rowIndex + 2, 0].Value.ToInt();
                            ri = rowIndex;
                        }
                    }
                }

                ws.Cells[1, 0, 1, cols["son"]].Borders[BordersIndex.EdgeBottom].Weight = BorderWeight.Thin;
                ws.Cells[2, 0].Select();
                ws.WindowInfo.FreezePanes = true;

                IRange ir2 = ws.Cells[0, cols["bd1"], ws.UsedRange.RowCount - 1, cols["bd2"]];
                ir2.Borders[BordersIndex.EdgeLeft].Weight =
                ir2.Borders[BordersIndex.EdgeRight].Weight =
                BorderWeight.Thin;


                //List<int> silinecek = new List<int>();
                //for (int col = ws.UsedRange.ColumnCount - 1; col >= 0; col--)
                //{
                //    bool removeCol = true;
                //    if (ws.Cells[0, col].Value.ToStr() == "SIZES" || ws.Cells[0, col].Value.ToStr() == "")
                //    {
                //        removeCol = false;
                //        break;
                //    }
                //    else
                //        for (int r = 2; r < ws.UsedRange.RowCount; r++)
                //            if (ws.Cells[r, col].Value.ToStr() != string.Empty)
                //            {
                //                removeCol = false;
                //                break;
                //            }

                //    if (removeCol)
                //        silinecek.Add(col);
                //}

                //foreach (int sil in silinecek)
                //{
                //    ws.Cells[0, sil, 1, sil].UnMerge();
                //    ws.Cells[0, sil].EntireColumn.Delete(DeleteShiftDirection.Left);
                //}

                switch (PLTemplateId)
                {
                    case 1:
                        ws.Cells[0, cols["total"]].Value = "total quant.";

                        ws.Cells[0, 1].EntireColumn.Insert(InsertShiftDirection.Right);
                        IRange ir3 = ws.Cells[0, 1, ws.UsedRange.RowCount - 1, 1];
                        ir3.Borders[BordersIndex.InsideHorizontal].Weight =
                        ir3.Borders[BordersIndex.InsideVertical].Weight = BorderWeight.Hairline;
                        ir3.Borders[BordersIndex.EdgeTop].Weight =
                        ir3.Borders[BordersIndex.EdgeBottom].Weight = BorderWeight.Thin;

                        r1 = ws.Cells[0, 0];
                        r2 = ws.Cells[1, 1];
                        ws.Cells[r1.Address + ":" + r2.Address].Merge();

                        string MaxBoxNo = /*"/"+*/ws.Cells[dtList.Rows.Count + 1, 0].Value.ToStr();
                        for (int rowIndex = 0; rowIndex < dtList.Rows.Count; rowIndex++)
                        {
                            r1 = ws.Cells[rowIndex + 2, 1];
                            r1.Value = MaxBoxNo;
                            r1.HorizontalAlignment = HAlign.Left;
                        }
                        //ws.Cells[0, 0].EntireColumn.HorizontalAlignment = HAlign.Right;
                        break;

                    case 5:
                        int satir = 2;
                        int basl = 2;
                        string EskiOrderNr = ws.Cells[satir, 1].Value.ToStr();
                        while (satir < ws.UsedRange.RowCount + 1)
                        {
                            if (ws.Cells[satir, 1].Value.ToStr() != EskiOrderNr)
                            {
                                EskiOrderNr = ws.Cells[satir, 1].Value.ToStr();
                                ws.Cells[satir, 1].EntireRow.Insert(InsertShiftDirection.Down);
                                ws.Cells[satir, 1].EntireRow.Interior.Color = Color.FromArgb(System.Drawing.Color.YellowGreen.ToArgb());
                                ws.Cells[satir, 1].RowHeight = 20;
                                ws.Cells[satir, 0].Value = ws.Cells[satir - 1, 0].Value;

                                for (int c = cols["bd1"]; c <= cols["bd2"] + 1; c++)
                                {
                                    ws.Cells[satir, c].Formula = "=SUM(" + ws.Cells[basl, c].Address + ":" + ws.Cells[satir - 1, c].Address + ")";
                                    Formatla1(ws.Cells[satir, c]);
                                }
                                ws.Cells[satir, cols["bd2"] + 2].Formula = "=" + ws.Cells[satir, cols["bd2"] + 1].Address + "*0.3";
                                ws.Cells[satir, cols["bd2"] + 3].Formula = "=" + ws.Cells[satir, cols["bd2"] + 2].Address + "+0.6";
                                ws.Cells[satir, cols["bd2"] + 4].Formula = "=" + ws.Cells[satir, 0].Address + "*0.6*0.4*0.4";

                                ws.Cells[satir, cols["bd2"] + 2, satir, cols["bd2"] + 4].NumberFormat = @"##0.00;[Red](##0.00)";
                                ws.Cells[satir, cols["bd2"] + 2, satir, cols["bd2"] + 4].VerticalAlignment = VAlign.Center;

                                basl = satir + 1;
                            }
                            satir++;
                        }
                        break;
                }

                ws.UsedRange.Columns.AutoFit();
                //ws.UsedRange.Rows.AutoFit();

                for (int col = 0; col < ws.UsedRange.ColumnCount; col++)
                {
                    if (!(ws.Cells[0, col].Value.ToStr() == "SIZES" ||
                        ws.Cells[0, col].Value.ToStr() == ""))
                        ws.Cells[0, col].ColumnWidth *= 1.2;
                    else
                        ws.Cells[0, col].ColumnWidth = 3.6;
                }
                for (int i = 2; i <= 4; i++)
                    ws.Cells[0, cols["bd2"] + i].ColumnWidth = 12;
            }

            wb.Worksheets[0].Delete();
            wb.Worksheets[0].Select();
        }


        public void Save()
        {
            string FN = Session.DataManager.ExecuteScalar("GetPLFilename", YuklemePartisiId).ToStr();
            string FileName = AppSession.InitialDirectory + "\\" + FN + ".xlsx";

            //SaveFileDialog saver = new SaveFileDialog();
            //if (AppSession.InitialDirectory != string.Empty)
            //    saver.InitialDirectory = AppSession.InitialDirectory;
            //else
            //    saver.InitialDirectory = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop);
            //string FN = Session.DataManager.ExecuteScalar("GetPLFilename", YuklemePartisiId).ToStr();
            //saver.FileName = FN;
            //saver.Filter = "Excel Dosyaları|*.xlsx";

            //if (saver.ShowDialog() == DialogResult.OK)
            //{
            //    AppSession.InitialDirectory = new System.IO.FileInfo(saver.FileName).DirectoryName;
            wb.SaveAs(FileName, FileFormat.OpenXMLWorkbook);
            wb.Close();

            DevTeam.FileManager.RunFile(FileName);
        }

        void Formatla1(IRange rx, bool isBold = false)
        {
            rx.WrapText =
            rx.Font.Bold = isBold;
            rx.VerticalAlignment = VAlign.Center;
            rx.HorizontalAlignment = HAlign.Center;
        }

    }
}